﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml.Linq;
using Model;
using MySqlConnector;

namespace DAL
{
    public class UserService
    {
        

        public User GetUserByName(string name)
        {
            string sql = "SELECT * FROM user, clubmanage WHERE user.userid = clubmanage.userid AND username ='"+name+"'";
            string connStr = "server=localhost;user=root;database=studentclubmis;port=3306;password=123456";
            MySqlConnection conn = null;
            User user = null;

            try
            {
                // 打开连接  
                conn = new MySqlConnection(connStr);
                conn.Open();
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                MySqlDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    int userid = Convert.ToInt32(dr["userid"]);
                    string username = dr["username"].ToString();
                    string password = dr["pwd"].ToString();
                    string role = dr["role"].ToString();
                    int clubid = Convert.ToInt32(dr["clubid"]);
                    user = new User(userid, password, role, userid, username);

                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
                //Console.WriteLine("连接失败: " + ex.Message);

            }
            finally
            {
                conn.Close();
            }
            return user;

        }

        public bool UpdateUser(User user)//修改社员信息
        {
            string sql = "Update user set username=@username,Pwd=@Pwd,Role=@Role where username=@username";
            MySqlParameter[] para = new MySqlParameter[3];
            para[0] = new MySqlParameter("@username", user.Username);
            para[1] = new MySqlParameter("@Pwd", user.Pwd);
            para[2] = new MySqlParameter("@Role", user.Role);
            try
            {
                int n = SQLHelper.ExecuteNonQuery(sql, para);
                if (n > 0)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }


        public bool AddUser(User user)//注册用户
        {
            string sql = "Insert into user values(@userid,@username,@Pwd,@Role)";
            MySqlParameter[] para = new MySqlParameter[4];
            para[0] = new MySqlParameter("@userid", user.Userid);
            para[1] = new MySqlParameter("@username", user.Username);
            para[2] = new MySqlParameter("@Pwd", user.Pwd);
            para[3] = new MySqlParameter("@Role", user.Role);


            string sql1 = "Insert into clubmanage values(@userid,@clubid)";
            MySqlParameter[] para1 = new MySqlParameter[2];
            para1[0] = new MySqlParameter("@userid", user.Userid);
            para1[1] = new MySqlParameter("@clubid", user.Clubid);
            try
            {
                int n = SQLHelper.ExecuteNonQuery(sql, para);
                int n1 = SQLHelper.ExecuteNonQuery(sql1, para1);
                if (n > 0 && n1> 0)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            
            
        }

        public ArrayList GetALLUsers(string sql)
        {
            
            string connStr = "server=localhost;user=root;database=studentclubmis;port=3306;password=123456";
            MySqlConnection conn = null;
            DataSet ds = new DataSet();
            ArrayList userList = null;
            try
            {
                conn = new MySqlConnection(connStr);
                conn.Open();
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                MySqlDataAdapter da = new MySqlDataAdapter();
                da.SelectCommand = cmd;
                da.Fill(ds, "users");
                if (ds.Tables["users"].Rows.Count > 0)
                {
                    userList = new ArrayList();
                    for (int i = 0; i < ds.Tables["users"].Rows.Count; i++)
                    {
                        User user = new User();
                        DataRow row = ds.Tables["users"].Rows[i];
                        user.Userid =int.Parse( row["userid"].ToString());
                        user.Username = row["username"].ToString();
                        user.Pwd = row["pwd"].ToString();
                        user.Role = row["role"].ToString();
                        user.Clubid =int.Parse(row["clubid"].ToString());
                        userList.Add(user);
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
                //Console.WriteLine("连接失败: " + ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return userList;
        }

        public bool DelUsers(User user)
        {
            string sql = "delete from user where username=@username";
            MySqlParameter[] para = new MySqlParameter[1];
            para[0] = new MySqlParameter("@username", user.Username);
            try
            {
                int n = SQLHelper.ExecuteNonQuery(sql, para);
                
                if (n > 0 )
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public bool UptUser(User user)
        {
            string sql = "update user set userid=@userid,username=@username,Pwd=@Pwd,Role=@Role where userid=@userid";
            MySqlParameter[] para = new MySqlParameter[4];
            para[0] = new MySqlParameter("@userid", user.Userid);
            para[1] = new MySqlParameter("@username", user.Username);
            para[2] = new MySqlParameter("@Pwd", user.Pwd);
            para[3] = new MySqlParameter("@Role", user.Role);
            try
            {
                int n = SQLHelper.ExecuteNonQuery(sql, para);

                if (n > 0)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
    }
}
